create table gen_table
(
    table_id          bigint                            not null,
    table_name        varchar(200)  default ''::character varying,
    table_comment     varchar(500)  default ''::character varying,
    sub_table_name    varchar(64)   default ''::character varying,
    sub_table_fk_name varchar(64)   default ''::character varying,
    class_name        varchar(100)  default ''::character varying,
    tpl_category      varchar(200)  default 'crud'::character varying,
    package_name      varchar(100)  default NULL::character varying,
    module_name       varchar(30)   default NULL::character varying,
    business_name     varchar(30)   default NULL::character varying,
    function_name     varchar(50)   default NULL::character varying,
    function_author   varchar(50)   default NULL::character varying,
    gen_type          char          default '0'::bpchar not null,
    gen_path          varchar(200)  default '/'::character varying,
    options           varchar(1000) default NULL::character varying,
    version           integer       default 0,
    create_by         bigint,
    create_time       timestamp,
    update_by         bigint,
    update_time       timestamp,
    remark            varchar(500)  default NULL::character varying,
    constraint gen_table_pk
        primary key (table_id)
);

comment on table gen_table is '代码生成业务表';

comment on column gen_table.table_id is '编号';

comment on column gen_table.table_name is '表名称';

comment on column gen_table.table_comment is '表描述';

comment on column gen_table.sub_table_name is '关联子表的表名';

comment on column gen_table.sub_table_fk_name is '子表关联的外键名';

comment on column gen_table.class_name is '实体类名称';

comment on column gen_table.tpl_category is '使用的模板（CRUD单表操作 TREE树表操作）';

comment on column gen_table.package_name is '生成包路径';

comment on column gen_table.module_name is '生成模块名';

comment on column gen_table.business_name is '生成业务名';

comment on column gen_table.function_name is '生成功能名';

comment on column gen_table.function_author is '生成功能作者';

comment on column gen_table.gen_type is '生成代码方式（0zip压缩包 1自定义路径）';

comment on column gen_table.gen_path is '生成路径（不填默认项目路径）';

comment on column gen_table.options is '其它生成选项';

comment on column gen_table.version is '乐观锁';

comment on column gen_table.create_by is '创建者';

comment on column gen_table.create_time is '创建时间';

comment on column gen_table.update_by is '更新者';

comment on column gen_table.update_time is '更新时间';

comment on column gen_table.remark is '备注';

create table gen_table_column
(
    column_id      bigint not null,
    table_id       bigint,
    column_name    varchar(200) default NULL::character varying,
    column_comment varchar(500) default NULL::character varying,
    column_type    varchar(100) default NULL::character varying,
    java_type      varchar(500) default NULL::character varying,
    java_field     varchar(200) default NULL::character varying,
    is_pk          char         default NULL::bpchar,
    is_increment   char         default NULL::bpchar,
    is_required    char         default NULL::bpchar,
    is_insert      char         default NULL::bpchar,
    is_edit        char         default NULL::bpchar,
    is_list        char         default NULL::bpchar,
    is_query       char         default NULL::bpchar,
    query_type     varchar(200) default 'EQ'::character varying,
    html_type      varchar(200) default NULL::character varying,
    dict_type      varchar(200) default ''::character varying,
    sort           integer,
    version        integer      default 0,
    create_by      bigint,
    create_time    timestamp,
    update_by      bigint,
    update_time    timestamp,
    constraint gen_table_column_pk
        primary key (column_id)
);

comment on table gen_table_column is '代码生成业务表字段';

comment on column gen_table_column.column_id is '编号';

comment on column gen_table_column.table_id is '归属表编号';

comment on column gen_table_column.column_name is '列名称';

comment on column gen_table_column.column_comment is '列描述';

comment on column gen_table_column.column_type is '列类型';

comment on column gen_table_column.java_type is 'JAVA类型';

comment on column gen_table_column.java_field is 'JAVA字段名';

comment on column gen_table_column.is_pk is '是否主键（1是）';

comment on column gen_table_column.is_increment is '是否自增（1是）';

comment on column gen_table_column.is_required is '是否必填（1是）';

comment on column gen_table_column.is_insert is '是否为插入字段（1是）';

comment on column gen_table_column.is_edit is '是否编辑字段（1是）';

comment on column gen_table_column.is_list is '是否列表字段（1是）';

comment on column gen_table_column.is_query is '是否查询字段（1是）';

comment on column gen_table_column.query_type is '查询方式（等于、不等于、大于、小于、范围）';

comment on column gen_table_column.html_type is '显示类型（文本框、文本域、下拉框、复选框、单选框、日期控件）';

comment on column gen_table_column.dict_type is '字典类型';

comment on column gen_table_column.sort is '排序';

comment on column gen_table_column.version is '乐观锁';

comment on column gen_table_column.create_by is '创建者';

comment on column gen_table_column.create_time is '创建时间';

comment on column gen_table_column.update_by is '更新者';

comment on column gen_table_column.update_time is '更新时间';

create table sys_client
(
    id             bigint not null,
    client_id      varchar(64)  default ''::character varying,
    client_key     varchar(32)  default ''::character varying,
    client_secret  varchar(255) default ''::character varying,
    grant_type     varchar(255) default ''::character varying,
    device_type    varchar(32)  default ''::character varying,
    active_timeout integer      default 1800,
    timeout        integer      default 604800,
    status         char         default '0'::bpchar,
    version        integer      default 0,
    del_flag       smallint     default 0,
    create_by      bigint,
    create_time    timestamp,
    update_by      bigint,
    update_time    timestamp,
    constraint sys_client_pk
        primary key (id)
);

comment on table sys_client is '系统授权表';

comment on column sys_client.id is '主建';

comment on column sys_client.client_id is '客户端id';

comment on column sys_client.client_key is '客户端key';

comment on column sys_client.client_secret is '客户端秘钥';

comment on column sys_client.grant_type is '授权类型';

comment on column sys_client.device_type is '设备类型';

comment on column sys_client.active_timeout is 'token活跃超时时间';

comment on column sys_client.timeout is 'token固定超时';

comment on column sys_client.status is '状态（1正常 0停用）';

comment on column sys_client.version is '乐观锁';

comment on column sys_client.del_flag is '删除标志（0代表存在 1代表删除）';

comment on column sys_client.create_by is '创建者';

comment on column sys_client.create_time is '创建时间';

comment on column sys_client.update_by is '更新者';

comment on column sys_client.update_time is '更新时间';

create table sys_config
(
    config_id    bigint                           not null,
    tenant_id    bigint       default '0'::bigint not null,
    config_name  varchar(100) default ''::character varying,
    config_key   varchar(100) default ''::character varying,
    config_value varchar(500) default ''::character varying,
    config_type  char         default 'N'::bpchar,
    version      integer      default 0,
    create_by    bigint,
    create_time  timestamp,
    update_by    bigint,
    update_time  timestamp,
    remark       varchar(500) default NULL::character varying,
    constraint sys_config_pk
        primary key (config_id)
);

comment on table sys_config is '参数配置表';

comment on column sys_config.config_id is '参数主键';

comment on column sys_config.tenant_id is '租户编号';

comment on column sys_config.config_name is '参数名称';

comment on column sys_config.config_key is '参数键名';

comment on column sys_config.config_value is '参数键值';

comment on column sys_config.config_type is '系统内置（Y是 N否）';

comment on column sys_config.version is '乐观锁';

comment on column sys_config.create_by is '创建者';

comment on column sys_config.create_time is '创建时间';

comment on column sys_config.update_by is '更新者';

comment on column sys_config.update_time is '更新时间';

comment on column sys_config.remark is '备注';

create table sys_dept
(
    dept_id     bigint not null,
    tenant_id   bigint       default 0,
    parent_id   bigint       default 0,
    ancestors   varchar(760) default ''::character varying,
    dept_name   varchar(30)  default ''::character varying,
    order_num   integer      default 0,
    leader      varchar(20)  default ''::character varying,
    phone       varchar(11)  default NULL::character varying,
    email       varchar(50)  default NULL::character varying,
    status      char         default '0'::bpchar,
    version     integer      default 0,
    del_flag    smallint     default 0,
    create_by   bigint,
    create_time timestamp,
    update_by   bigint,
    update_time timestamp,
    constraint sys_dept_pk
        primary key (dept_id)
);

comment on table sys_dept is '部门表';

comment on column sys_dept.dept_id is '部门ID';

comment on column sys_dept.tenant_id is '租户编号';

comment on column sys_dept.parent_id is '父部门ID';

comment on column sys_dept.ancestors is '祖级列表';

comment on column sys_dept.dept_name is '部门名称';

comment on column sys_dept.order_num is '显示顺序';

comment on column sys_dept.leader is '负责人';

comment on column sys_dept.phone is '联系电话';

comment on column sys_dept.email is '邮箱';

comment on column sys_dept.status is '部门状态（1正常 0停用）';

comment on column sys_dept.version is '乐观锁';

comment on column sys_dept.del_flag is '删除标志（0代表存在 1代表删除）';

comment on column sys_dept.create_by is '创建者';

comment on column sys_dept.create_time is '创建时间';

comment on column sys_dept.update_by is '更新者';

comment on column sys_dept.update_time is '更新时间';

create table sys_dict_data
(
    dict_code   bigint                           not null,
    tenant_id   bigint       default '0'::bigint not null,
    dict_sort   integer      default 0,
    dict_label  varchar(100) default ''::character varying,
    dict_value  varchar(100) default ''::character varying,
    dict_type   varchar(100) default ''::character varying,
    css_class   varchar(100) default NULL::character varying,
    list_class  varchar(100) default NULL::character varying,
    is_default  char         default 'N'::bpchar,
    version     integer      default 0,
    create_by   bigint,
    create_time timestamp,
    update_by   bigint,
    update_time timestamp,
    remark      varchar(500) default NULL::character varying,
    constraint sys_dict_data_pk
        primary key (dict_code)
);

comment on table sys_dict_data is '字典数据表';

comment on column sys_dict_data.dict_code is '字典编码';

comment on column sys_dict_data.tenant_id is '租户编号';

comment on column sys_dict_data.dict_sort is '字典排序';

comment on column sys_dict_data.dict_label is '字典标签';

comment on column sys_dict_data.dict_value is '字典键值';

comment on column sys_dict_data.dict_type is '字典类型';

comment on column sys_dict_data.css_class is '样式属性（其他样式扩展）';

comment on column sys_dict_data.list_class is '表格回显样式';

comment on column sys_dict_data.is_default is '是否默认（Y是 N否）';

comment on column sys_dict_data.version is '乐观锁';

comment on column sys_dict_data.create_by is '创建者';

comment on column sys_dict_data.create_time is '创建时间';

comment on column sys_dict_data.update_by is '更新者';

comment on column sys_dict_data.update_time is '更新时间';

comment on column sys_dict_data.remark is '备注';

create table sys_dict_type
(
    dict_id     bigint                           not null,
    tenant_id   bigint       default '0'::bigint not null,
    dict_name   varchar(100) default ''::character varying,
    dict_type   varchar(100) default ''::character varying,
    version     integer      default 0,
    create_by   bigint,
    create_time timestamp,
    update_by   bigint,
    update_time timestamp,
    remark      varchar(500) default NULL::character varying,
    constraint sys_dict_type_pk
        primary key (dict_id)
);

comment on table sys_dict_type is '字典类型表';

comment on column sys_dict_type.dict_id is '字典主键';

comment on column sys_dict_type.tenant_id is '租户编号';

comment on column sys_dict_type.dict_name is '字典名称';

comment on column sys_dict_type.dict_type is '字典类型';

comment on column sys_dict_type.version is '乐观锁';

comment on column sys_dict_type.create_by is '创建者';

comment on column sys_dict_type.create_time is '创建时间';

comment on column sys_dict_type.update_by is '更新者';

comment on column sys_dict_type.update_time is '更新时间';

comment on column sys_dict_type.remark is '备注';

create unique index sys_dict_type_index1
    on sys_dict_type (tenant_id, dict_type);


create table sys_import_export_t
(
    id          bigint                      not null,
    name        varchar(255),
    url         varchar(500),
    oss_id      bigint,
    log_info    text,
    status      char    default '1'::bpchar not null,
    tenant_id   bigint  default '0'::bigint not null,
    version     integer default 0           not null,
    create_by   bigint,
    create_time timestamp,
    update_by   bigint,
    update_time timestamp,
    constraint sys_import_export_pk
        primary key (id)
);

comment on table sys_import_export_t is '导入导出记录';

comment on column sys_import_export_t.id is '唯一编码';

comment on column sys_import_export_t.name is '文件名称';

comment on column sys_import_export_t.url is '文件地址';

comment on column sys_import_export_t.oss_id is '文件Id';

comment on column sys_import_export_t.log_info is '日志信息';

comment on column sys_import_export_t.status is '状态(1正常  0异常 2部分正常)';

comment on column sys_import_export_t.tenant_id is '租户id';

comment on column sys_import_export_t.version is '乐观锁';

comment on column sys_import_export_t.create_by is '创建人';

comment on column sys_import_export_t.create_time is '创建时间';

comment on column sys_import_export_t.update_by is '更新人';

comment on column sys_import_export_t.update_time is '更新时间';



create table sys_logininfor
(
    info_id        bigint                           not null,
    tenant_id      bigint       default '0'::bigint not null,
    user_name      varchar(50)  default ''::character varying,
    client_key     varchar(32)  default ''::character varying,
    device_type    varchar(32)  default ''::character varying,
    ipaddr         varchar(128) default ''::character varying,
    login_location varchar(255) default ''::character varying,
    browser        varchar(50)  default ''::character varying,
    os             varchar(50)  default ''::character varying,
    status         char         default '1'::bpchar,
    msg            varchar(255) default ''::character varying,
    login_time     timestamp,
    constraint sys_logininfor_pk
        primary key (info_id)
);

comment on table sys_logininfor is '系统访问记录';

comment on column sys_logininfor.info_id is '访问ID';

comment on column sys_logininfor.tenant_id is '租户编号';

comment on column sys_logininfor.user_name is '用户账号';

comment on column sys_logininfor.client_key is '客户端';

comment on column sys_logininfor.device_type is '设备类型';

comment on column sys_logininfor.ipaddr is '登录IP地址';

comment on column sys_logininfor.login_location is '登录地点';

comment on column sys_logininfor.browser is '浏览器类型';

comment on column sys_logininfor.os is '操作系统';

comment on column sys_logininfor.status is '登录状态（1成功 0失败）';

comment on column sys_logininfor.msg is '提示消息';

comment on column sys_logininfor.login_time is '访问时间';

create index idx_sys_logininfor_lt
    on sys_logininfor (login_time);

create index idx_sys_logininfor_s
    on sys_logininfor (status);

create table sys_menu
(
    menu_id        bigint      not null,
    menu_name      varchar(50) not null,
    parent_id      bigint       default 0,
    order_num      integer      default 0,
    path           varchar(200) default ''::character varying,
    component      varchar(255) default NULL::character varying,
    query_param    varchar(255) default NULL::character varying,
    is_frame       char         default '0'::bpchar,
    is_cache       char         default '1'::bpchar,
    menu_type      char         default ''::bpchar,
    visible        char         default '1'::bpchar,
    status         char         default '1'::bpchar,
    perms          varchar(100) default NULL::character varying,
    icon           varchar(100) default ''::character varying,
    version        integer      default 0,
    create_by      bigint,
    create_time    timestamp,
    update_by      bigint,
    update_time    timestamp,
    remark         varchar(500) default ''::character varying,
    component_name varchar(255) default NULL::character varying,
    constraint sys_menu_pk
        primary key (menu_id)
);

comment on table sys_menu is '菜单权限表';

comment on column sys_menu.menu_id is '菜单ID';

comment on column sys_menu.menu_name is '菜单名称';

comment on column sys_menu.parent_id is '父菜单ID';

comment on column sys_menu.order_num is '显示顺序';

comment on column sys_menu.path is '路由地址';

comment on column sys_menu.component is '组件路径';

comment on column sys_menu.query_param is '路由参数';

comment on column sys_menu.is_frame is '是否为外链（1是 0否）';

comment on column sys_menu.is_cache is '是否缓存（1缓存 0不缓存）';

comment on column sys_menu.menu_type is '菜单类型（M目录 C菜单 F按钮）';

comment on column sys_menu.visible is '显示状态（1显示 0隐藏）';

comment on column sys_menu.status is '菜单状态（1正常 0停用）';

comment on column sys_menu.perms is '权限标识';

comment on column sys_menu.icon is '菜单图标';

comment on column sys_menu.version is '乐观锁';

comment on column sys_menu.create_by is '创建者';

comment on column sys_menu.create_time is '创建时间';

comment on column sys_menu.update_by is '更新者';

comment on column sys_menu.update_time is '更新时间';

comment on column sys_menu.remark is '备注';

comment on column sys_menu.component_name is '组件名称';

create table sys_notice
(
    notice_id      bigint                           not null,
    tenant_id      bigint       default '0'::bigint not null,
    notice_title   varchar(50)                      not null,
    notice_type    char                             not null,
    notice_content text,
    status         char         default '1'::bpchar,
    version        integer      default 0,
    create_by      bigint,
    create_time    timestamp,
    update_by      bigint,
    update_time    timestamp,
    remark         varchar(255) default NULL::character varying,
    constraint sys_notice_pk
        primary key (notice_id)
);

comment on table sys_notice is '通知公告表';

comment on column sys_notice.notice_id is '公告ID';

comment on column sys_notice.tenant_id is '租户编号';

comment on column sys_notice.notice_title is '公告标题';

comment on column sys_notice.notice_type is '公告类型（1通知 2公告）';

comment on column sys_notice.notice_content is '公告内容';

comment on column sys_notice.status is '公告状态（1正常 0关闭）';

comment on column sys_notice.version is '乐观锁';

comment on column sys_notice.create_by is '创建者';

comment on column sys_notice.create_time is '创建时间';

comment on column sys_notice.update_by is '更新者';

comment on column sys_notice.update_time is '更新时间';

comment on column sys_notice.remark is '备注';

create table sys_oper_log
(
    oper_id        bigint                            not null,
    tenant_id      bigint        default '0'::bigint not null,
    title          varchar(50)   default ''::character varying,
    business_type  integer       default 0,
    method         varchar(100)  default ''::character varying,
    request_method varchar(10)   default ''::character varying,
    operator_type  integer       default 0,
    oper_name      varchar(50)   default ''::character varying,
    dept_name      varchar(50)   default ''::character varying,
    oper_url       varchar(255)  default ''::character varying,
    oper_ip        varchar(128)  default ''::character varying,
    oper_location  varchar(255)  default ''::character varying,
    oper_param     varchar(2000) default ''::character varying,
    json_result    varchar(2000) default ''::character varying,
    status         integer       default 1,
    error_msg      varchar(2000) default ''::character varying,
    oper_time      timestamp,
    cost_time      bigint        default 0,
    constraint sys_oper_log_pk
        primary key (oper_id)
);

comment on table sys_oper_log is '操作日志记录';

comment on column sys_oper_log.oper_id is '日志主键';

comment on column sys_oper_log.tenant_id is '租户编号';

comment on column sys_oper_log.title is '模块标题';

comment on column sys_oper_log.business_type is '业务类型（0其它 1新增 2修改 3删除）';

comment on column sys_oper_log.method is '方法名称';

comment on column sys_oper_log.request_method is '请求方式';

comment on column sys_oper_log.operator_type is '操作类别（0其它 1后台用户 2手机端用户）';

comment on column sys_oper_log.oper_name is '操作人员';

comment on column sys_oper_log.dept_name is '部门名称';

comment on column sys_oper_log.oper_url is '请求URL';

comment on column sys_oper_log.oper_ip is '主机地址';

comment on column sys_oper_log.oper_location is '操作地点';

comment on column sys_oper_log.oper_param is '请求参数';

comment on column sys_oper_log.json_result is '返回参数';

comment on column sys_oper_log.status is '操作状态（1正常 0异常）';

comment on column sys_oper_log.error_msg is '错误消息';

comment on column sys_oper_log.oper_time is '操作时间';

comment on column sys_oper_log.cost_time is '消耗时间';

create index idx_sys_oper_log_bt
    on sys_oper_log (business_type);

create index idx_sys_oper_log_ot
    on sys_oper_log (oper_time);

create index idx_sys_oper_log_s
    on sys_oper_log (status);

create table sys_oss
(
    oss_id        bigint                                     not null,
    tenant_id     bigint       default '0'::bigint           not null,
    file_name     varchar(255) default ''::character varying not null,
    original_name varchar(255) default ''::character varying not null,
    file_suffix   varchar(10)  default ''::character varying not null,
    url           varchar(500) default ''::character varying not null,
    service       varchar(20)  default 'minio'::character varying,
    version       integer      default 0,
    create_by     bigint,
    create_time   timestamp,
    update_by     bigint,
    update_time   timestamp,
    constraint sys_oss_pk
        primary key (oss_id)
);

comment on table sys_oss is 'OSS对象存储表';

comment on column sys_oss.oss_id is '对象存储主键';

comment on column sys_oss.tenant_id is '租户编码';

comment on column sys_oss.file_name is '文件名';

comment on column sys_oss.original_name is '原名';

comment on column sys_oss.file_suffix is '文件后缀名';

comment on column sys_oss.url is 'URL地址';

comment on column sys_oss.service is '服务商';

comment on column sys_oss.version is '乐观锁';

comment on column sys_oss.create_by is '上传人';

comment on column sys_oss.create_time is '创建时间';

comment on column sys_oss.update_by is '更新者';

comment on column sys_oss.update_time is '更新时间';

create table sys_oss_config
(
    oss_config_id bigint                                     not null,
    tenant_id     bigint       default '0'::bigint           not null,
    config_key    varchar(20)  default ''::character varying not null,
    access_key    varchar(255) default ''::character varying,
    secret_key    varchar(255) default ''::character varying,
    bucket_name   varchar(255) default ''::character varying,
    prefix        varchar(255) default ''::character varying,
    endpoint      varchar(255) default ''::character varying,
    domain_name   varchar(255) default ''::character varying,
    is_https      char         default 'N'::bpchar,
    region        varchar(255) default ''::character varying,
    access_policy char         default '1'::bpchar           not null,
    status        char         default '1'::bpchar,
    ext1          varchar(255) default ''::character varying,
    version       integer      default 0,
    create_by     bigint,
    create_time   timestamp,
    update_by     bigint,
    update_time   timestamp,
    remark        varchar(500) default ''::character varying,
    constraint sys_oss_config_pk
        primary key (oss_config_id)
);

comment on table sys_oss_config is '对象存储配置表';

comment on column sys_oss_config.oss_config_id is '主建';

comment on column sys_oss_config.tenant_id is '租户编码';

comment on column sys_oss_config.config_key is '配置key';

comment on column sys_oss_config.access_key is 'accessKey';

comment on column sys_oss_config.secret_key is '秘钥';

comment on column sys_oss_config.bucket_name is '桶名称';

comment on column sys_oss_config.prefix is '前缀';

comment on column sys_oss_config.endpoint is '访问站点';

comment on column sys_oss_config.domain_name is '自定义域名';

comment on column sys_oss_config.is_https is '是否https（Y=是,N=否）';

comment on column sys_oss_config.region is '域';

comment on column sys_oss_config.access_policy is '桶权限类型(0=private 1=public 2=custom)';

comment on column sys_oss_config.status is '是否默认（1=是,0=否）';

comment on column sys_oss_config.ext1 is '扩展字段';

comment on column sys_oss_config.version is '乐观锁';

comment on column sys_oss_config.create_by is '创建者';

comment on column sys_oss_config.create_time is '创建时间';

comment on column sys_oss_config.update_by is '更新者';

comment on column sys_oss_config.update_time is '更新时间';

comment on column sys_oss_config.remark is '备注';

create table sys_post
(
    post_id     bigint                           not null,
    tenant_id   bigint       default '0'::bigint not null,
    post_code   varchar(64)                      not null,
    post_name   varchar(50)                      not null,
    post_sort   integer                          not null,
    status      char                             not null,
    version     integer      default 0,
    create_by   bigint,
    create_time timestamp,
    update_by   bigint,
    update_time timestamp,
    remark      varchar(500) default NULL::character varying,
    constraint sys_post_pk
        primary key (post_id)
);

comment on table sys_post is '岗位信息表';

comment on column sys_post.post_id is '岗位ID';

comment on column sys_post.tenant_id is '租户编号';

comment on column sys_post.post_code is '岗位编码';

comment on column sys_post.post_name is '岗位名称';

comment on column sys_post.post_sort is '显示顺序';

comment on column sys_post.status is '状态（0正常 1停用）';

comment on column sys_post.version is '乐观锁';

comment on column sys_post.create_by is '创建者';

comment on column sys_post.create_time is '创建时间';

comment on column sys_post.update_by is '更新者';

comment on column sys_post.update_time is '更新时间';

comment on column sys_post.remark is '备注';

create table sys_role
(
    role_id             bigint                           not null,
    tenant_id           bigint       default 0,
    role_name           varchar(30)                      not null,
    role_key            varchar(100)                     not null,
    role_sort           integer                          not null,
    data_scope          char         default '1'::bpchar,
    menu_check_strictly boolean      default true,
    dept_check_strictly boolean      default true,
    status              char         default '1'::bpchar not null,
    version             integer      default 0,
    del_flag            smallint     default 0,
    create_by           bigint,
    create_time         timestamp,
    update_by           bigint,
    update_time         timestamp,
    remark              varchar(500) default NULL::character varying,
    constraint sys_role_pk
        primary key (role_id)
);

comment on table sys_role is '角色信息表';

comment on column sys_role.role_id is '角色ID';

comment on column sys_role.tenant_id is '租户编号';

comment on column sys_role.role_name is '角色名称';

comment on column sys_role.role_key is '角色权限字符串';

comment on column sys_role.role_sort is '显示顺序';

comment on column sys_role.data_scope is '数据范围（1：全部数据权限 2：自定数据权限 3：本部门数据权限 4：本部门及以下数据权限）';

comment on column sys_role.menu_check_strictly is '菜单树选择项是否关联显示';

comment on column sys_role.dept_check_strictly is '部门树选择项是否关联显示';

comment on column sys_role.status is '角色状态（1正常 0停用）';

comment on column sys_role.version is '乐观锁';

comment on column sys_role.del_flag is '删除标志（0代表存在 1代表删除）';

comment on column sys_role.create_by is '创建者';

comment on column sys_role.create_time is '创建时间';

comment on column sys_role.update_by is '更新者';

comment on column sys_role.update_time is '更新时间';

comment on column sys_role.remark is '备注';

create table sys_role_dept
(
    role_id bigint not null,
    dept_id bigint not null,
    constraint sys_role_dept_pk
        primary key (role_id, dept_id)
);

comment on table sys_role_dept is '角色和部门关联表';

comment on column sys_role_dept.role_id is '角色ID';

comment on column sys_role_dept.dept_id is '部门ID';

create table sys_role_menu
(
    role_id bigint not null,
    menu_id bigint not null,
    constraint sys_role_menu_pk
        primary key (role_id, menu_id)
);

comment on table sys_role_menu is '角色和菜单关联表';

comment on column sys_role_menu.role_id is '角色ID';

comment on column sys_role_menu.menu_id is '菜单ID';

create table sys_tenant_package
(
    package_id          bigint not null,
    package_name        varchar(20)   default ''::character varying,
    menu_ids            varchar(3000) default ''::character varying,
    remark              varchar(200)  default ''::character varying,
    menu_check_strictly boolean       default true,
    status              char          default '1'::bpchar,
    version             integer       default 0,
    del_flag            smallint      default 0,
    create_by           bigint,
    create_time         timestamp,
    update_by           bigint,
    update_time         timestamp,
    primary key (package_id)
);

comment on table sys_tenant_package is '租户套餐表';

comment on column sys_tenant_package.package_id is '租户套餐id';

comment on column sys_tenant_package.package_name is '套餐名称';

comment on column sys_tenant_package.menu_ids is '关联菜单id';

comment on column sys_tenant_package.remark is '备注';

comment on column sys_tenant_package.status is '状态（1正常 0停用）';

comment on column sys_tenant_package.version is '乐观锁';

comment on column sys_tenant_package.del_flag is '删除标志（0代表存在 1代表删除）';

comment on column sys_tenant_package.create_by is '创建者';

comment on column sys_tenant_package.create_time is '创建时间';

comment on column sys_tenant_package.update_by is '更新者';

comment on column sys_tenant_package.update_time is '更新时间';

create table sys_tenant
(
    tenant_id         bigint not null,
    contact_user_name varchar(20)  default NULL::character varying,
    contact_phone     varchar(20)  default NULL::character varying,
    company_name      varchar(50)  default NULL::character varying,
    license_number    varchar(30)  default NULL::character varying,
    address           varchar(200) default NULL::character varying,
    intro             varchar(200) default NULL::character varying,
    domain            varchar(200) default NULL::character varying,
    remark            varchar(200) default NULL::character varying,
    package_id        bigint,
    expire_time       timestamp,
    account_count     integer      default '-1'::integer,
    status            char         default '1'::bpchar,
    version           integer      default 0,
    del_flag          smallint     default 0,
    create_by         bigint,
    create_time       timestamp,
    update_by         bigint,
    update_time       timestamp,
    constraint pk_sys_tenant
        primary key (tenant_id)
);

comment on table sys_tenant is '租户表';

comment on column sys_tenant.tenant_id is '租户编号';

comment on column sys_tenant.contact_user_name is '联系人';

comment on column sys_tenant.contact_phone is '联系电话';

comment on column sys_tenant.company_name is '企业名称';

comment on column sys_tenant.license_number is '统一社会信用代码';

comment on column sys_tenant.address is '地址';

comment on column sys_tenant.intro is '企业简介';

comment on column sys_tenant.domain is '域名';

comment on column sys_tenant.remark is '备注';

comment on column sys_tenant.package_id is '租户套餐编号';

comment on column sys_tenant.expire_time is '过期时间';

comment on column sys_tenant.account_count is '用户数量（-1不限制）';

comment on column sys_tenant.status is '租户状态（1正常 0停用）';

comment on column sys_tenant.version is '乐观锁';

comment on column sys_tenant.del_flag is '删除标志（0代表存在 1代表删除）';

comment on column sys_tenant.create_by is '创建者';

comment on column sys_tenant.create_time is '创建时间';

comment on column sys_tenant.update_by is '更新者';

comment on column sys_tenant.update_time is '更新时间';

create table sys_user
(
    user_id     bigint                                     not null,
    tenant_id   bigint,
    dept_id     bigint,
    user_name   varchar(30)                                not null,
    nick_name   varchar(30)                                not null,
    user_type   varchar(10)  default 'sys_user'::character varying,
    email       varchar(50)  default ''::character varying not null,
    phonenumber varchar(11)  default ''::character varying,
    gender      char         default '0'::bpchar,
    avatar      bigint,
    password    varchar(100) default ''::character varying,
    status      char         default '1'::bpchar,
    version     integer      default 0,
    del_flag    smallint     default 0,
    login_ip    varchar(128) default ''::character varying,
    login_date  timestamp,
    create_by   bigint,
    create_time timestamp,
    update_by   bigint,
    update_time timestamp,
    remark      varchar(500) default NULL::character varying,
    constraint sys_user_pk
        primary key (user_id)
);

comment on table sys_user is '用户信息表';

comment on column sys_user.user_id is '用户ID';

comment on column sys_user.tenant_id is '租户编号';

comment on column sys_user.dept_id is '部门ID';

comment on column sys_user.user_name is '用户账号';

comment on column sys_user.nick_name is '用户昵称';

comment on column sys_user.user_type is '用户类型（sys_user系统用户）';

comment on column sys_user.email is '用户邮箱';

comment on column sys_user.phonenumber is '手机号码';

comment on column sys_user.gender is '用户性别（0男 1女 2未知）';

comment on column sys_user.avatar is '头像地址';

comment on column sys_user.password is '密码';

comment on column sys_user.status is '帐号状态（1正常 0停用）';

comment on column sys_user.version is '乐观锁';

comment on column sys_user.del_flag is '删除标志（0代表存在 1代表删除）';

comment on column sys_user.login_ip is '最后登陆IP';

comment on column sys_user.login_date is '最后登陆时间';

comment on column sys_user.create_by is '创建者';

comment on column sys_user.create_time is '创建时间';

comment on column sys_user.update_by is '更新者';

comment on column sys_user.update_time is '更新时间';

comment on column sys_user.remark is '备注';

create unique index sys_user_unqindex_tenant_username
    on sys_user (tenant_id, user_name);

create table sys_user_post
(
    user_id bigint not null,
    post_id bigint not null,
    constraint sys_user_post_pk
        primary key (user_id, post_id)
);

comment on table sys_user_post is '用户与岗位关联表';

comment on column sys_user_post.user_id is '用户ID';

comment on column sys_user_post.post_id is '岗位ID';

create table sys_user_role
(
    user_id bigint not null,
    role_id bigint not null,
    constraint sys_user_role_pk
        primary key (user_id, role_id)
);

comment on table sys_user_role is '用户和角色关联表';

comment on column sys_user_role.user_id is '用户ID';

comment on column sys_user_role.role_id is '角色ID';

create function cast_varchar_to_timestamp(character varying) returns timestamp with time zone
    strict
    language sql
as
$$
select to_timestamp($1, 'yyyy-mm-dd hh24:mi:ss');
$$;

create function find_in_set(value anyelement, string_list text) returns integer
    language plpgsql
as
$$
DECLARE
    position INTEGER;
BEGIN
    IF string_list = '' THEN
        RETURN 0;
    ELSE
        position := array_position(string_to_array(string_list, ','), value::TEXT);
        RETURN position;
    END IF;
END;
$$;

